﻿/*
 * 
 * Author: dogvane
 * CreateDate:
 * 
 * Chage:
 * 2009-11-20 dogvane
 * 1.增加数据库表是否已经创建过的判断
 * 
 * 2009-10-19 dogvane
 * 1.增加一个日志的内容，日志记录内容为 3x3 一共九条。
 * 
 */

using System;
using System.Collections.Generic;
using System.Text;

namespace LogicLayer
{
    /// <summary>
    /// Sqlite 的数据库表创建
    /// </summary>
    public class CreateTabel
    {
        /// <summary>
        /// 创建数据库表
        /// </summary>
        public void Create()
        {
            CreateDiaryContent();
            CreateDiaryStatus();
            CreateDiaryCatalog();
        }

        /// <summary>
        /// 删除数据库文件
        /// </summary>
        public void DropTable()
        {
            System.IO.File.Delete("db.s3db");
        }

        /// <summary>
        /// 检查数据库表是否已经创建过
        /// </summary>
        /// <param name="tableName">数据库表名称</param>
        /// <returns>
        /// true  表已经存在
        /// false 表未创建
        /// </returns>
        bool CheckTable(string tableName)
        {
            var ds = Common.SQLiteHelper.ExecuteDataset(
                string.Format("select count(*) from sqlite_master where name='{0}'", tableName));
            if (System.Convert.ToInt32(ds.Tables[0].Rows[0][0]) == 1)
                return true;
            return false;
        }

        void CreateDiaryContent()
        {
            if (CheckTable("DiaryContent"))
                return;

            string sql = @"
CREATE TABLE [DiaryContent] (
[ID] INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL,
[Date] DATE DEFAULT CURRENT_DATE NULL,
[Content] TEXT  NULL,
[CatalogID] INTEGER  NULL
)
";
            Common.SQLiteHelper.ExecuteNonQuery(sql);
        }

        void CreateDiaryStatus()
        {
            if (CheckTable("DiaryStatus"))
                return;

            string sql = @"
CREATE TABLE [DiaryStatus] (
[ID] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,
[Date] DATE DEFAULT CURRENT_DATE NOT NULL,
[Weather] NVARCHAR(50)  NULL,
[Emotion] NVARCHAR(150)  NULL,
[Health] NVARCHAR(150)  NULL,
[Wakeup] TIMESTAMP  NULL,
[Sleep] TIMESTAMP  NULL
);
";
            Common.SQLiteHelper.ExecuteNonQuery(sql);
        }


        void CreateDiaryCatalog()
        {
            if (CheckTable("DiaryCatalog"))
                return;

            string sql = @"
CREATE TABLE [DiaryCatalog] (
[ID] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,
[DayType] INTEGER DEFAULT '0' NOT NULL,
[Title] NVARCHAR(30)  NULL
)";
            Common.SQLiteHelper.ExecuteNonQuery(sql);            
        }
    }
}
